package org.hiucung.desktoptranslationtools.utils

import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.ss.usermodel.IndexedColors
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.hiucung.desktoptranslationtools.bean.RowResult
import java.io.File
import java.io.FileOutputStream
import java.io.InputStream
import kotlin.collections.iterator

/**
 * @title: TranslationExcelExporter
 * @projectName DesktopTranslationTools
 * @description: Excel 文件的生成、样式复制、多语言翻译导出
 * @author Kicc
 * @date 2025/7/24 15:11
 */
object TranslationExcelExporter {
    fun writeToExcel(
        rows: List<RowResult>,
        languages: Set<String>,
        defaultLanguage: String = "en",
        fileName: File
    ) {

        println("TranslationExcelExporter: progress-->0")

        val bookSample: InputStream? =
            object {}.javaClass.classLoader.getResourceAsStream("sample.xlsx")
        val sampleWorkbook: Workbook = XSSFWorkbook(bookSample)
        // 创建一个新的工作簿
        val book = XSSFWorkbook()
        // 复制所有工作表
        for (i in 0 until sampleWorkbook.numberOfSheets) {
            val sampleSheet: Sheet = sampleWorkbook.getSheetAt(i)
            val newSheet = book.createSheet(sampleSheet.sheetName)
            copyEntireSheet(sampleSheet, newSheet, sampleWorkbook, book)
        }

        println("TranslationExcelExporter: progress-->10")

        val stringSheet: Sheet = book.getSheetAt(1)
        val row: Row = stringSheet.getRow(0)
        val moduleCell = row.getCell(0)
        val maxLengthCell = row.getCell(1)
        val nameCell = row.getCell(2)
        val localCodesMap = mutableMapOf<String, Int>()
        for ((index, value) in languages.withIndex()) {
            localCodesMap[value] = index + 3
            val title = if (value == "default") {
                LanguageUtil.findByLanguageCode(defaultLanguage)
                    ?.let { "${it.englishName}(${value})" } ?: run { value }
            } else {
                LanguageUtil.findByLanguageCode(value)?.let { "${it.englishName}(${value})" }
                    ?: run { value }
            }
            row.createCell(index + 3).apply { cellStyle = nameCell.cellStyle }
                .setCellValue(title)
        }
        // 自动调整每一列的宽度
        for (colIndex in 0 until row.physicalNumberOfCells) {
            stringSheet.autoSizeColumn(colIndex) // 自动调整每一列的宽度
        }
        // 创建条件格式规则
        val sheetConditionalFormatting = stringSheet.sheetConditionalFormatting

//    patternFormatting.fillPattern = FillPatternType.SOLID_FOREGROUND


        val translationCellStyle = book.createCellStyle()
        val format = book.createDataFormat()
        translationCellStyle.dataFormat = format.getFormat("@") // "@" 格式表示文本格式

        println("TranslationExcelExporter: progress-->20")
        for ((index, value) in rows.withIndex()) {
            val iRow = stringSheet.createRow(index + 1)
            val imoduleCell = iRow.createCell(0)
            val imaxLengthCell = iRow.createCell(1)
            val inameCell = iRow.createCell(2)



            imoduleCell.setCellValue(value.moduleName)
            numberCellFormula(imaxLengthCell)
//        imaxLengthCell.setCellValue(value.maxLength.toString())
            inameCell.setCellValue(value.name)
            // 条件公式：=AND(LEN(D2)>=$B2,LEN(D2)<>0)
            for ((lKey, lValue) in value.language) {
                if (localCodesMap.containsKey(lKey) && localCodesMap[lKey] != null) {
                    iRow.createCell(localCodesMap[lKey]!!).apply {
                        cellStyle = translationCellStyle
                    }.setCellValue(lValue)
                }
            }
            for (lcIndex in 0..localCodesMap.keys.size) {
                val lIndex = lcIndex + 3
                val columnLetterIndex = lIndex + 1
                val condition = sheetConditionalFormatting.createConditionalFormattingRule(
                    "AND(LEN(${
                        getColumnLetter(columnLetterIndex)
                    }${iRow.rowNum + 1})>B${iRow.rowNum + 1}, LEN(${
                        getColumnLetter(
                            columnLetterIndex
                        )
                    }${iRow.rowNum + 1})<>0)"
                )
                // 设置背景颜色为红色
                val patternFormatting = condition.createPatternFormatting()
                patternFormatting.fillBackgroundColor = IndexedColors.RED.index

                // 设置条件格式应用的范围，这里是 D2
                val regions = arrayOf(
                    CellRangeAddress(
                        iRow.rowNum,
                        iRow.rowNum,
                        lIndex,
                        lIndex
                    )
                )  // D列是索引3，从第2行到第2行

                // 添加条件格式到工作表
                sheetConditionalFormatting.addConditionalFormatting(regions, condition)
            }

        }
        sampleWorkbook.close()
        FileOutputStream(fileName).use { fileOut ->
            try {
                book.write(fileOut)  // 写入数据
                fileOut.flush()      // 确保数据写入
            } catch (e: Exception) {
                e.printStackTrace()  // 处理异常，查看是否有文件写入错误
            } finally {
                fileOut.close()      // 明确关闭文件输出流
            }
        }
        book.close()
        println("TranslationExcelExporter: progress-->100")
    }

    // 复制整个工作表，包括内容、样式、合并单元格和条件格式
    fun copyEntireSheet(
        sourceSheet: Sheet,
        targetSheet: Sheet,
        sourceWorkbook: Workbook,
        targetWorkbook: Workbook
    ) {
        for (i in 0 until sourceSheet.getRow(0).lastCellNum) {
            targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i))
        }

        for (i in 0..sourceSheet.lastRowNum) {
            val sourceRow = sourceSheet.getRow(i)
            val targetRow = targetSheet.createRow(i)

            if (sourceRow != null) {
                targetRow.height = sourceRow.height
                for (j in 0 until sourceRow.lastCellNum) {
                    val sourceCell = sourceRow.getCell(j)
                    val targetCell = targetRow.createCell(j)

                    if (sourceCell != null) {
                        targetCell.cellStyle =
                            copyCellStyle(sourceCell.cellStyle, sourceWorkbook, targetWorkbook)
                        when (sourceCell.cellType) {
                            CellType.STRING -> targetCell.setCellValue(sourceCell.stringCellValue)
                            CellType.NUMERIC -> targetCell.setCellValue(sourceCell.numericCellValue)
                            CellType.BOOLEAN -> targetCell.setCellValue(sourceCell.booleanCellValue)
                            CellType.FORMULA -> targetCell.cellFormula = sourceCell.cellFormula
                            CellType.BLANK -> targetCell.setBlank()
                            else -> {}
                        }
                    }
                }
            }
        }

        for (i in 0 until sourceSheet.numMergedRegions) {
            val mergedRegion = sourceSheet.getMergedRegion(i)
            targetSheet.addMergedRegion(mergedRegion)
        }

//    copyConditionalFormatting(sourceSheet, targetSheet)
    }

    // 复制样式到新 Workbook 中
    fun copyCellStyle(
        sourceStyle: CellStyle,
        sourceWorkbook: Workbook,
        targetWorkbook: Workbook
    ): CellStyle {
        val newStyle = targetWorkbook.createCellStyle()
        newStyle.cloneStyleFrom(sourceStyle)
        return newStyle
    }

    fun numberCellFormula(cell: Cell) {//长度计算逻辑
        val formula =
            "IF(LEN(D${cell.rowIndex + 1})>LEN(E${cell.rowIndex + 1}),LEN(D${cell.rowIndex + 1}),LEN(E${cell.rowIndex + 1}))"
        cell.cellFormula = formula
    }

    private fun getColumnLetter(colIndex: Int): String {
        var column = colIndex
        val letters = StringBuilder()

        while (column > 0) {
            column-- // Adjust for 1-based index
            letters.append((column % 26 + 'A'.code).toChar())
            column /= 26
        }

        return letters.reverse().toString()
    }
}